


local file_path_in "/tochange/"

set more off

timer clear 1
timer on 1

*************************************************************************************** 
* create a shock measure for a builder-region using the changes in house prices during crisis in other counties (or zips)
*************************************************************************************** 

** do this at both the county and zipcode level
local X FIPS_CODE zip
local Y fips zip
local Z County Zip
local R FIPS_CODE zipcode

local n : word count `X'
forvalues i=1/`n' {
	local xi : word `i' of `X'
	local yi : word `i' of `Y'
	local zi : word `i' of `Z'
	local ri : word `i' of `R'
	
	display " `xi' `yi' `zi' `ri'"
	
	use "`file_path_in'/builder county/rawBUILDERmergedHP_`yi'.dta", clear   
	cap drop _merge
	`nosmall'
	`infull'
	if "`xi'"=="FIPS_CODE" {
		keep if error==0
		keep if small==0
		drop if nfipsyr<=2
	}
	
			
	**************** create index of shock measures, then merge back to house level data with original shock measure

	** create builder level changes in house prices
	collapse (sum) nsold_`yi' (mean) saleamount_`yi' (sum) totalsale_`yi', by(year seller `xi' `ri')    /* saleamount is total revenue from a county in a given year. nsold is total sales in a county in a year  */

	bysort seller `xi' year: gen avgpr_`yi' = totalsale_`yi' / nsold_`yi'  /* average sale price of seller j in county k in year t */

	local yearlist 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 
	foreach yr of local yearlist {
		bysort seller `xi' : gen temp`yr' = avgpr_`yi' if year==20`yr'  // seller's average sale price in a given county in year t
		bysort seller `xi' : egen avg`yr'=max(temp`yr') 
		drop temp`yr'
	}

	local shocklist 0104 0204 0205 0305 0306 0607 0608 0609 0709 0708 0508 0509  0809 0912 1013 1114 
	foreach c of local shocklist {
		local wgtbg `=substr("`c'",1,2)'
		local wgtend `=substr("`c'",3,4)'
		gen chg`c'_`yi' = ((avg`wgtend' - avg`wgtbg')/avg`wgtbg')  // seller's log change in house prices between years 
	}	

	foreach t of local yearlist {
		bysort seller year: gen totalsold`t'_`yi'  = sum(nsold_`yi') if year==20`t'
		bysort seller year: replace totalsold`t'_`yi'  = totalsold`t'_`yi'[_N] if year==20`t'

		bysort seller year: gen temp`t' = (nsold_`yi'/totalsold`t'_`yi') if year==20`t' 
		bysort seller `xi' : egen weight`t'_`yi' = max(temp`t')  if year==20`t'   
	}

	*** merge in Zillow county level house prices 
	if "`xi'"=="FIPS_CODE" {
		tostring FIPS_CODE, replace
		replace FIPS_CODE = "0"+FIPS_CODE if strlen(FIPS_CODE)==4
		destring FIPS_CODE, replace
	}
	
	tempfile temp0
	preserve
		use "`file_path_in'/ZillowHp`zi'.dta", clear 
		rename houseprice hpZIL_`yi'
		sort `ri' date
		collapse (mean) hpZIL_`yi', by(year `ri')
		local yrlist 01 02 03 04 05 06 07 08 09 10 11 12 13 14 
		foreach t of local yrlist {
			bysort `ri': egen temp`t' = mean(hpZIL_`yi') if year==20`t'
			bysort `ri': egen hpZIL_`yi'`t' = max(temp`t')
		}	
		bysort `xi': gen delta0104ZIL_`yi' = ((hpZIL_`yi'04 - hpZIL_`yi'01)/hpZIL_`yi'01)
		bysort `xi': gen delta0204ZIL_`yi' = ((hpZIL_`yi'04 - hpZIL_`yi'02)/hpZIL_`yi'02)
		bysort `xi': gen delta0205ZIL_`yi' = ((hpZIL_`yi'05 - hpZIL_`yi'02)/hpZIL_`yi'02)
		bysort `xi': gen delta0305ZIL_`yi' = ((hpZIL_`yi'05 - hpZIL_`yi'03)/hpZIL_`yi'03)
		bysort `xi': gen delta0306ZIL_`yi' = ((hpZIL_`yi'06 - hpZIL_`yi'03)/hpZIL_`yi'03)
		bysort `xi': gen delta0607ZIL_`yi' = ((hpZIL_`yi'07 - hpZIL_`yi'06)/hpZIL_`yi'06)  
		bysort `xi': gen delta0608ZIL_`yi' = ((hpZIL_`yi'08 - hpZIL_`yi'06)/hpZIL_`yi'06)  
		bysort `xi': gen delta0609ZIL_`yi' = ((hpZIL_`yi'09 - hpZIL_`yi'06)/hpZIL_`yi'06)  
		bysort `xi': gen delta0709ZIL_`yi' = ((hpZIL_`yi'09 - hpZIL_`yi'07)/hpZIL_`yi'07) 
		bysort `xi': gen delta0708ZIL_`yi' = ((hpZIL_`yi'08 - hpZIL_`yi'07)/hpZIL_`yi'07) 
		bysort `xi': gen delta0509ZIL_`yi' = ((hpZIL_`yi'09 - hpZIL_`yi'05)/hpZIL_`yi'05) 
		bysort `xi': gen delta0508ZIL_`yi' = ((hpZIL_`yi'08 - hpZIL_`yi'08)/hpZIL_`yi'08) 
		bysort `xi': gen delta0809ZIL_`yi' = ((hpZIL_`yi'09 - hpZIL_`yi'08)/hpZIL_`yi'08) 
		
		bysort `xi': gen delta0912ZIL_`yi' = ((hpZIL_`yi'12 - hpZIL_`yi'09)/hpZIL_`yi'09) 
		bysort `xi': gen delta1013ZIL_`yi' = ((hpZIL_`yi'13 - hpZIL_`yi'10)/hpZIL_`yi'10) 
		bysort `xi': gen delta1114ZIL_`yi' = ((hpZIL_`yi'14 - hpZIL_`yi'11)/hpZIL_`yi'11) 
		
		drop temp*
		save `temp0', replace
	restore	
	merge m:1 `ri' year using `temp0', keep(master match) 
	rename _merge zillowmatch_`yi'
	
	** calculate state
	if "`xi'"=="FIPS_CODE" {
		tostring FIPS_CODE, gen(fips)
		gen fips_len=strlen(fips)
		replace fips = "0"+fips if fips_len==4
		gen state = substr(fips,1,2)
		destring state, replace
	}
	
	local shocklist 0104 0204 0205 0305 0306 0607 0608 0609 0709 0708 0508 0509  0809 0912 1013 1114 
	foreach c of local shocklist {
		di "`c'"
		preserve
			drop if delta`c'ZIL_`yi'==.    /* drop counties which don't have zillow prices */
			
			** create shock measure
			local wgt `=substr("`c'",1,2)'
			di "`wgt'"
			keep if year==20`wgt'
			gen depdelta`c'ZIL_`yi' = weight`wgt'_`yi'*(delta`c'ZIL_`yi')   /* delta1yr can be any measure of house price performance */
			bysort seller year: gen dep4_`c'_`yi' = sum(depdelta`c'ZIL_`yi')
			bysort seller year: replace dep4_`c'_`yi' = dep4_`c'_`yi'[_N]
			bysort seller year: gen shockdelta`c'ZIL_`yi' = dep4_`c'_`yi'-depdelta`c'ZIL_`yi'  /* this represents the builder's exposure to 06-09 hp's in all other counties except the county of that row */
			*drop dep4_`c'_`yi'
			
			replace shockdelta`c'ZIL_`yi' = 0 if seller=="ACTUALPERSON"  
			bysort seller year: gen nsold`wgt'_`yi' = _n
			bysort seller year: replace nsold`wgt'_`yi' = nsold`wgt'_`yi'[_N]
			
			if "`xi'"=="FIPS_CODE" {
				keep seller `xi' year shock* totalsold`wgt'_`yi' chg`c'_`yi' delta`c'ZIL_`yi' nsold`wgt'_`yi' weight* depdelta`c'ZIL_`yi' dep4_`c'_`yi' state
				*** calculate shock only over counties in a different state
				bysort seller state: gen dep5 = sum(depdelta`c'ZIL_`yi')
				bysort seller state: replace dep5 = dep5[_N]
				gen shockdelta`c'ZIL_noadj_`yi' = dep4_`c'_`yi' - dep5  
				rename state state_fips
			}
			
			else {
				keep seller `xi' year shock* totalsold`wgt'_`yi' chg`c'_`yi' delta`c'ZIL_`yi' nsold`wgt'_`yi' weight* depdelta`c'ZIL_`yi' dep4_`c'_`yi' 
			}

			save "`file_path_in'//temp_br`c'.dta", replace
		restore	
	}	

	local shocklist2 0104 0204 0205 0305 0306  0608 0609 0709 0708 0508 0509  0809 0912 1013 1114 
	use "`file_path_in'/temp_br0607.dta", clear
	foreach m of local shocklist2 {
		merge m:1 seller `xi' using "`file_path_in'/temp_br`m'.dta", keep(match master) nogen
	}
	label var shockdelta0609ZIL_`yi' "\Delta (HP_k)_{06-09}(other)"
	label var shockdelta0205ZIL_`yi' "\Delta (HP_k)_{02-05}(other)"
	label var delta0609ZIL_`yi' "\Delta (HP_k)_{06-09}" 
	label var delta0205ZIL_`yi' "\Delta (HP_k)_{02-05}"
	 
	egen shockdecile2_`yi' = xtile(shockdelta0609ZIL_`yi') , n(10)
	egen shockdecile3_`yi' = xtile(shockdelta0609ZIL_`yi') if shockdelta0609ZIL_`yi'!=0 , n(10)
	
	saveold "`file_path_in'/builder county/rawBUILDERmergedHPshockAGGR_`yi'.dta", version(13) replace   
}

timer off 1
timer list 1
